package com.htc.callcare.database;

import java.io.File;

import com.htc.callcare.infrastructure.globalVar;
import com.htc.callcare.model.NoteModel;
import com.htc.callcare.model.recordModel;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.content.ContentValues;

public class Database extends SQLiteOpenHelper {
	private static final int DB_VERSION = 1;
	private static final String DB_NAME = "Call_Care_DB";
	private static final String TABLE_CALL_RECORD_CHECK = "Call_Record_Check_Table";
	private static final String TABLE_CALL_RECORD_NOTE = "Call_Record_Note_Table";
	private static final String TABLE_CALL_RECORD_NEW = "Call_Record_NewTable";
	private static final String TABLE_VOICE_MAIL_SERVICE = "Voice_Mail_Service_Table";
	private static final String TABLE_VOICE_MAIL_NEW = "Voice_Mail_New_Table";
	private static final String TABLE_VOICE_MAIL_CHECK = "Voice_Mail_Table_Check";
	// TABLE_CALL_RECORD_CHECK
	private static final String RECORD_ID = "id";
	private static final String RECORD_CALL_TYPE = "callType";
	private static final String RECORD_CALL_TIME = "callTime";
	private static final String RECORD_CALL_DAY = "callDay";
	private static final String RECORD_CALL_NUMBER = "callNumber";
	private static final String RECORD_NOTE = "note";
	private static final String RECORD_IS_CHECK = "isCheck";
	private static final String RECORD_FILE_URI = "fileUri";
	private static final String RECORD_FILE_NEW = "new";
	// TABLE_CALL_RECORD_NOTE
	private static final String NOTE_ID = "id";
	private static final String NOTE_CONTENT = "noteContent";
	private static final String NOTE_FILR_URI = "fileUri";
	// TABLE_VOICE_MAIL_SERVICE
	private static final String VOICE_MAIL_ID = "id";
	private static final String VOICE_MAIL_PHONENUMBER = "phoneNumber";
	private static final String VOICE_MAIL_WAIT_TIMEOUT = "wait";// 1:Late
																	// 0:NoLate
	private static final String VOICE_MAIL_SEND_SMS = "sms";
	// TABLE_VOICE_MAIL_NEW
	private static final String VOICE_MAIL_FILE_URI = "fileUri";
	private static final String VOICE_MAIL_NEW = "new";
	// TABLE_VOICE_MAIL_CHECK

	private static final String CREATE_TABLE_CALL_RECORD_CHECK = "CREATE TABLE "
			+ TABLE_CALL_RECORD_CHECK
			+ " ("
			+ RECORD_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ RECORD_CALL_TYPE
			+ " INTEGER, "
			+ RECORD_CALL_TIME
			+ " TEXT, "
			+ RECORD_CALL_DAY
			+ " TEXT, "
			+ RECORD_CALL_NUMBER
			+ " TEXT, "
			+ RECORD_NOTE
			+ " TEXT, "
			+ RECORD_IS_CHECK
			+ " INTEGER, "
			+ RECORD_FILE_URI
			+ " TEXT);";

	private static final String CREATE_TABLE_CALL_RECORD_NOTE = "CREATE TABLE "
			+ TABLE_CALL_RECORD_NOTE + " (" + NOTE_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + NOTE_CONTENT + " TEXT, "
			+ NOTE_FILR_URI + " TEXT);";
	
	private static final String CREATE_TABLE_RECORD_NEW = "CREATE TABLE "
			+ TABLE_CALL_RECORD_NEW + " (" + RECORD_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + RECORD_FILE_URI
			+ " TEXT," + RECORD_FILE_NEW + " INTEGER);";

	private static final String CREATE_VOICE_MAIL_SERVICE_TABLE = "CREATE TABLE "
			+ TABLE_VOICE_MAIL_SERVICE
			+ " ("
			+ VOICE_MAIL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ VOICE_MAIL_PHONENUMBER
			+ " TEXT, "
			+ VOICE_MAIL_WAIT_TIMEOUT
			+ " INTEGER, "
			+ VOICE_MAIL_SEND_SMS + " INTEGER);";

	private static final String CREATE_TABLE_VOICE_MAIL_NEW = "CREATE TABLE "
			+ TABLE_VOICE_MAIL_NEW + " (" + VOICE_MAIL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + VOICE_MAIL_FILE_URI
			+ " TEXT," + VOICE_MAIL_NEW + " INTEGER);";

	private static final String CREATE_TABLE_VOICE_MAIL_CHECK = "CREATE TABLE "
			+ TABLE_VOICE_MAIL_CHECK + " (" + VOICE_MAIL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + VOICE_MAIL_FILE_URI
			+ " TEXT);";

	public Database(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.v("SQL", "Creating database");
		db.execSQL(CREATE_TABLE_CALL_RECORD_CHECK);
		db.execSQL(CREATE_TABLE_CALL_RECORD_NOTE);
		db.execSQL(CREATE_VOICE_MAIL_SERVICE_TABLE);
		db.execSQL(CREATE_TABLE_VOICE_MAIL_NEW);
		db.execSQL(CREATE_TABLE_VOICE_MAIL_CHECK);
		db.execSQL(CREATE_TABLE_RECORD_NEW);
		Log.v("SQL", "Creating database sucessfully");
		onUpgrade(db, db.getVersion(), DB_VERSION);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	public int findRecordNew(String fileUri) {
		int isNew = -1;
		final SQLiteDatabase db = getReadableDatabase();
		String[]columns = new String[]{RECORD_ID, RECORD_FILE_URI, RECORD_FILE_NEW};
		String whereClause = RECORD_FILE_URI+"=?";
		String[]whereArgs = new String[]{String.valueOf(fileUri)};
		final Cursor cursor = db.query(TABLE_CALL_RECORD_NEW, columns, whereClause, whereArgs, null, null, null);
		if(cursor.getCount()>0){
			while(cursor.moveToNext()){
				isNew = cursor.getInt(cursor.getColumnIndex(RECORD_FILE_NEW));
			}
		}
		cursor.close();
		db.close();
		return isNew;
	}
	
	public void addRecordNew(String fileUri) {
		// If not exist in database
		if (findRecordNew(fileUri) == -1) {
			final SQLiteDatabase db = getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(RECORD_FILE_URI, fileUri);
			values.put(RECORD_FILE_NEW, 1);
			db.insert(TABLE_CALL_RECORD_NEW, null, values);
			db.close();
		}
	}
	
	public void updateRecordNewIsRead(String fileUri) {
		if (findRecordNew(fileUri) == 1) {
			final SQLiteDatabase db = getReadableDatabase();
			final ContentValues values = new ContentValues();
			values.put(RECORD_FILE_NEW, 0);
			final String selection = RECORD_FILE_URI + "=?";
			final String[] whereArgs = new String[] { String.valueOf(fileUri) };
			db.update(TABLE_CALL_RECORD_NEW, values, selection, whereArgs);
			db.close();
		}
	}
	
	public void deleteRecordNew(String fileUri){
		final SQLiteDatabase db = getReadableDatabase();
		final String whereClause = RECORD_FILE_URI+"=?";
		final String[] whereArgs = new String[]{String.valueOf(fileUri)};
		db.delete(TABLE_CALL_RECORD_NEW, whereClause, whereArgs);
		db.close();
	}

	

	
////////////////////////////TABLE_VOICE_MAIL_CHECK//////////////////////////////
	// Add check to database
	public void addVoiceMailCheck(String fileUri) {
		final SQLiteDatabase db = getReadableDatabase();
		final ContentValues values = new ContentValues();
		values.put(VOICE_MAIL_FILE_URI, fileUri);
		db.insert(TABLE_VOICE_MAIL_CHECK, null, values);
		db.close();
	}

	// true if it exist in data base
	public boolean findVoiceMailCheck(String fileUri) {
		final SQLiteDatabase db = getReadableDatabase();
		String[] columns = new String[] { VOICE_MAIL_ID, VOICE_MAIL_FILE_URI };
		String whereClause = VOICE_MAIL_FILE_URI + "=?";
		String[] whereArgs = new String[] { String.valueOf(fileUri) };
		final Cursor cursor = db.query(TABLE_VOICE_MAIL_CHECK, columns,
				whereClause, whereArgs, null, null, null);
		if (cursor.getCount() != 0) {
			cursor.close();
			db.close();
			return true;

		} else {
			cursor.close();
			db.close();
			return false;
		}
	}
	
	public void deleteVoiceMailCheck(String fileUri) {
			final SQLiteDatabase db = getReadableDatabase();
			final String whereClause = VOICE_MAIL_FILE_URI + "=?";
			final String[] whereArgs = new String[] { String.valueOf(fileUri) };
			db.delete(TABLE_VOICE_MAIL_CHECK, whereClause, whereArgs);
			db.close();
	}
	
	public int countAllVocieMailCheck(){
		final SQLiteDatabase db = getReadableDatabase();
		int count = 0;
		String[] columns = new String[] { VOICE_MAIL_ID, VOICE_MAIL_FILE_URI };
		final Cursor cursor =  db.query(TABLE_VOICE_MAIL_CHECK, columns, "", null, null, null, null, null);
		if (cursor.getCount()!=0) {
			while(cursor.moveToNext()){
				count++;
			}
		}
		cursor.close();
		db.close();
		return count;
	}

	// TABLE_VOICE_MAIL_NEW STORE NEW VOICE MAIL UPDATE
	// 1 if exist in database && New
	// 0 if exist in database && not New
	// -1 if not exist in database
	public int findVoiceMailNew(String fileUri) {
		int isNew = -1;
		final SQLiteDatabase db = getReadableDatabase();
		String[] columns = new String[] { VOICE_MAIL_ID, VOICE_MAIL_FILE_URI,VOICE_MAIL_NEW };
		String selection = VOICE_MAIL_FILE_URI + "=?";
		String[] whereArgs = new String[] { String.valueOf(fileUri) };
		final Cursor cursor = db.query(TABLE_VOICE_MAIL_NEW, columns,
				selection, whereArgs, null, null, null);
		if (cursor.getCount() > 0) {
			while (cursor.moveToNext()) {
				isNew = cursor.getInt(cursor.getColumnIndex(VOICE_MAIL_NEW));
			}
		}
		cursor.close();
		db.close();
		return isNew;
	}

	public void addVoiceMailNew(String fileUri) {
		// If not exist in database
		if (findVoiceMailNew(fileUri) == -1) {
			final SQLiteDatabase db = getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(VOICE_MAIL_FILE_URI, fileUri);
			values.put(VOICE_MAIL_NEW, 1);
			db.insert(TABLE_VOICE_MAIL_NEW, null, values);
			db.close();
		}
	}

	// Update to 0 if database is read
	public void updateVoiceMailNewIsRead(String fileUri) {
		if (findVoiceMailNew(fileUri) == 1) {
			final SQLiteDatabase db = getReadableDatabase();
			final ContentValues values = new ContentValues();
			values.put(VOICE_MAIL_NEW, 0);
			final String selection = VOICE_MAIL_FILE_URI + "=?";
			final String[] whereArgs = new String[] { String.valueOf(fileUri) };
			db.update(TABLE_VOICE_MAIL_NEW, values, selection, whereArgs);
			db.close();
		}
	}

	public int countNewVoiceMail() {
		int count = 0;
		final SQLiteDatabase db = getReadableDatabase();
		final String[] columns = new String[] { VOICE_MAIL_ID,
				VOICE_MAIL_FILE_URI, VOICE_MAIL_NEW };
		final String whereClause = VOICE_MAIL_NEW + "=?";
		final String[] whereArgs = new String[] { String.valueOf(1) };
		final Cursor cursor = db.query(TABLE_VOICE_MAIL_NEW, columns,
				whereClause, whereArgs, null, null, null);

		if (cursor.getCount() > 0) {
			while (cursor.moveToNext()) {
				count++;
			}
		}
		return count;
	}

	public void deleteVoiceMailNew(String fileUri){
		final SQLiteDatabase db = getReadableDatabase();
		final String whereClause = VOICE_MAIL_FILE_URI+"=?";
		final String[] whereArgs = new String[]{String.valueOf(fileUri)};
		db.delete(TABLE_VOICE_MAIL_NEW, whereClause, whereArgs);
		db.close();
	}
	// //////////////////////////////////////////////////////////////

	// VOICE_MAIL_SERVICE_TABLE CHECK TIME OUT VOCIE MAIL OF ONE NUMBER
	// True if this number exist in database
	// False if this number not exist in database
	public boolean findVoiceMailNumber(String number) {
		final SQLiteDatabase db = getReadableDatabase();
		final String[] columns = new String[] { VOICE_MAIL_ID,
				VOICE_MAIL_PHONENUMBER, VOICE_MAIL_WAIT_TIMEOUT,
				VOICE_MAIL_SEND_SMS };
		final String whereClause = VOICE_MAIL_PHONENUMBER + "=?";
		final String[] whereArgs = new String[] { String.valueOf(number) };
		final Cursor cursor = db.query(TABLE_VOICE_MAIL_SERVICE, columns,
				whereClause, whereArgs, null, null, null);
		if (cursor.getCount() != 0) {
			db.close();
			return true;
		} else {
			db.close();
			return false;
		}

	}

	// Add number to database
	public void addVoiceMailNumber(String number) {
		if (!findVoiceMailNumber(number)) {
			final SQLiteDatabase db = getReadableDatabase();
			final ContentValues values = new ContentValues();
			values.put(VOICE_MAIL_PHONENUMBER, number);
			values.put(VOICE_MAIL_SEND_SMS, 0);
			values.put(VOICE_MAIL_WAIT_TIMEOUT, 0);
			db.insert(TABLE_VOICE_MAIL_SERVICE, null, values);
			db.close();
		}

	}

	// Delete voice mail number from database
	public void deleteVoiceMailNumber(String number) {
		final SQLiteDatabase db = getReadableDatabase();
		final String whereClause = VOICE_MAIL_PHONENUMBER + "=?";
		final String[] whereArgs = new String[] { String.valueOf(number) };
		db.delete(TABLE_VOICE_MAIL_SERVICE, whereClause, whereArgs);
		db.close();

	}

	// Update database after send Message send = 1, wait = 0;
	public void updateSendMessage(String number, int value) {
		final SQLiteDatabase db = getReadableDatabase();
		final ContentValues values = new ContentValues();
		values.put(VOICE_MAIL_SEND_SMS, value);
		final String whereClause = VOICE_MAIL_PHONENUMBER + "=?";
		final String[] whereArgs = new String[] { String.valueOf(number) };
		db.update(TABLE_VOICE_MAIL_SERVICE, values, whereClause, whereArgs);
		db.close();
	}

	// Update database when time out
	public void updateTimeOut(String number, int value) {
		final SQLiteDatabase db = getReadableDatabase();
		final ContentValues values = new ContentValues();
		values.put(VOICE_MAIL_WAIT_TIMEOUT, value);
		final String whereClause = VOICE_MAIL_PHONENUMBER + "=?";
		final String[] whereArgs = new String[] { String.valueOf(number) };
		db.update(TABLE_VOICE_MAIL_SERVICE, values, whereClause, whereArgs);
		db.close();
	}

	// check voice mail wait time out
	public boolean checkVoiceMailWaitTimeOut(String number) {
		final SQLiteDatabase db = getReadableDatabase();
		final String whereClause = VOICE_MAIL_PHONENUMBER + "=?";
		final String[] whereArgs = new String[] { String.valueOf(number) };
		final String[] columns = new String[] { VOICE_MAIL_ID,
				VOICE_MAIL_PHONENUMBER, VOICE_MAIL_SEND_SMS,
				VOICE_MAIL_WAIT_TIMEOUT };
		final Cursor cursor = db.query(TABLE_VOICE_MAIL_SERVICE, columns,
				whereClause, whereArgs, null, null, null);
		while (cursor.moveToNext()) {
			if (1 == cursor.getInt(cursor
					.getColumnIndex(VOICE_MAIL_WAIT_TIMEOUT))) {
				return true;
			} else {
				return false;
			}
		}
		return true;
	}

	// ////////////////////////////////////////////////////////////////////////////////////////////
	public boolean addNote(NoteModel model) {
		final SQLiteDatabase db = getWritableDatabase();
		final ContentValues values = new ContentValues();
		values.put(NOTE_CONTENT, model.getNoteContent());
		values.put(NOTE_FILR_URI, model.getFileUri());
		try {
			db.insert(TABLE_CALL_RECORD_NOTE, null, values);
			db.close();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			db.close();
			return false;
		}
	}

	public String findNote(String fileUri) {
		String result = "";
		final SQLiteDatabase db = getReadableDatabase();
		final String[] columns = new String[] { NOTE_ID, NOTE_CONTENT,
				NOTE_FILR_URI };
		final String whereClause = NOTE_FILR_URI + "=?";
		final String[] whereArgs = new String[] { String.valueOf(fileUri) };
		final Cursor cursor = db.query(TABLE_CALL_RECORD_NOTE, columns,
				whereClause, whereArgs, null, null, null);
		while (cursor.moveToNext()) {
			result = cursor.getString(cursor.getColumnIndex(NOTE_CONTENT));
		}
		cursor.close();
		db.close();
		return result;
	}

	public boolean updateNote(String fileUri, String newContent) {
		final SQLiteDatabase db = getReadableDatabase();
		final String whereClause = NOTE_FILR_URI + "=?";
		final String[] whereArgs = new String[] { fileUri };
		ContentValues contentValues = new ContentValues();
		contentValues.put(NOTE_CONTENT, newContent);
		db.update(TABLE_CALL_RECORD_NOTE, contentValues, whereClause, whereArgs);
		db.close();
		return false;
	}

	// ////////////////////////////////////////////////////////
	// STORE CHECK ITEM IN DB
	public boolean addCheckRecordToDatabase(recordModel model) {
		final SQLiteDatabase db = getWritableDatabase();
		final ContentValues values = new ContentValues();
		values.put(RECORD_CALL_TYPE, model.getCallType());
		values.put(RECORD_CALL_TIME, model.getCallType());
		values.put(RECORD_CALL_DAY, model.getCallDay());
		values.put(RECORD_CALL_NUMBER, model.callNumber);
		values.put(RECORD_NOTE, model.getNote());
		values.put(RECORD_IS_CHECK, 1);
		values.put(RECORD_FILE_URI, model.getFileUri());
		try {
			db.insert(TABLE_CALL_RECORD_CHECK, null, values);
			db.close();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			db.close();
			return false;
		}
	}

	// check Item if it is checked?
	// true if is check
	// else not check
	public boolean findRecordCheck(String fileUri) {
		final SQLiteDatabase db = getReadableDatabase();
		final String[] columns = new String[] { RECORD_ID, RECORD_CALL_TYPE, RECORD_CALL_TIME,
				RECORD_CALL_DAY, RECORD_CALL_NUMBER, RECORD_NOTE, RECORD_IS_CHECK, RECORD_FILE_URI };
		final String whereClause = RECORD_FILE_URI + "=?";
		final String[] whereArgs = new String[] { String.valueOf(fileUri) };
		final Cursor cursor = db.query(TABLE_CALL_RECORD_CHECK, columns,
				whereClause, whereArgs, null, null, null);
		if (cursor.getCount() != 0) {
			return true;
		}
		cursor.close();
		db.close();
		return false;
	}

	// DeleteCheckItem
	public void deleteCheckRecord(String fileUri) {
		final SQLiteDatabase db = getWritableDatabase();
		final String whereClause = RECORD_FILE_URI + "=?";
		final String[] whereArgs = new String[] { String.valueOf(fileUri) };
		db.delete(TABLE_CALL_RECORD_CHECK, whereClause, whereArgs);
		db.close();
	}
	
	
	public int countAllRecordCheck(){
		final SQLiteDatabase db = getReadableDatabase();
		int count = 0;
		final String[] columns = new String[] { RECORD_ID, RECORD_CALL_TYPE, RECORD_CALL_TIME,
				RECORD_CALL_DAY, RECORD_CALL_NUMBER, RECORD_NOTE, RECORD_IS_CHECK, RECORD_FILE_URI };
		final Cursor cursor =  db.query(TABLE_CALL_RECORD_CHECK, columns, "", null, null, null, null, null);
		if (cursor.getCount()!=0) {
			while(cursor.moveToNext()){
				count++;
			}
		}
		cursor.close();
		db.close();
		return count;
	}

}
